package com.example.shopping_web_back.mapper;

import com.example.shopping_web_back.pojo.MonthlySalesNumDTO;
import com.example.shopping_web_back.pojo.MonthlySalesPriceDTO;
import com.example.shopping_web_back.pojo.OrdersOfGoods;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface OrderMapper {

    List<OrdersOfGoods> list(String customerName);

    @Insert("insert into shoppingweb.order_of_goods (customer_name, business_name, goods_name, buy_time, price, goods_num, arrive_time, shipping_time, status, email_time, delivery_time, email, telephone, business_image) value (#{customerName}, #{businessName}, #{goodsName}, #{buyTime}, #{price}, #{goodsNum}, #{arriveTime}, #{shippingTime}, #{status}, #{emailTime}, #{deliveryTime}, #{email}, #{telephone}, #{businessImage})")
    void addOrder(OrdersOfGoods ordersOfGoods);

    @Delete("delete from shoppingweb.order_of_goods where customer_name=#{customerName} and business_name=#{businessName} and goods_name=#{goodsName} and buy_time=#{buyTime}")
    void deleteOrder(OrdersOfGoods ordersOfGoods);

    void updateOrder(String arriveTime, String emailTime, String shippingTime, String deliveryTime, String status, String buyTime, String businessName, String goodsName, String customerName);

    List<OrdersOfGoods> listBusiness(String businessName);

    @Select("select SUM(goods_num * price) from shoppingweb.order_of_goods where business_name=#{businessName}")
    Double gotTotalSalesPrice(String businessName);

    @Select("select count(*) from shoppingweb.order_of_goods where business_name=#{businessName}")
    Integer gotTotalSalesNum(String businessName);

    @Select("select DATE_FORMAT(arrive_time, '%Y-%m') AS month, SUM(goods_num) AS totalSalesNum "
            + "from shoppingweb.order_of_goods "
            + "where arrive_time > CURDATE() - INTERVAL 6 MONTH and business_name = #{businessName} "
            + "GROUP BY month "
            + "ORDER BY month DESC")
    List<MonthlySalesNumDTO> gotSixMonthSalesNum(String businessName);

    @Select("select DATE_FORMAT(arrive_time, '%Y-%m') AS month, SUM(goods_num * price) AS totalSalesPrice "
            + "from shoppingweb.order_of_goods "
            + "where arrive_time > CURDATE() - INTERVAL 6 MONTH and business_name = #{businessName} "
            + "GROUP BY month "
            + "ORDER BY month DESC")
    List<MonthlySalesPriceDTO> gotSixMonthSalesPrice(String businessName);
}
